package com.txtsqlclient.dataaccess.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.txtsqlclient.dataaccess.BaseDAO;
import com.txtsqlclient.dataaccess.DAOException;

/**
 * This class is required to execute various SQL queries using the database connection.
 * 
 * @author spadda
 */
public class QueryExecutionDAO implements BaseDAO {

    /** The database connection. */
    private Connection dbCon;

    /**
     * Constructor
     * 
     * @param con
     */
    public QueryExecutionDAO(Connection con) {
        this.dbCon = con;
    }

    /**
     * This method is required to close the database connection.
     * 
     * @throws DAOException
     */
    public void close() throws DAOException {
        try {
            this.dbCon.close();
        } catch (SQLException sqle) {
            throw new DAOException("Error while closing database connection", sqle);
        }
    }

    /**
     * This method is required to read the data from the database (using SELECT).
     * 
     * @param query
     * @return a list of lists of strings, representing the table of the results.
     * @throws DAOException
     */
    public List<List<String>> read(String query) throws DAOException {
        List<List<String>> result = null;
        Statement stmt = null;
        try {
            result = new ArrayList<List<String>>();
            stmt = this.dbCon.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            int numOfCols = rsMetaData.getColumnCount();
            for (int i = 0; i < numOfCols; i++) {
                List<String> column = new ArrayList<String>();
                column.add(rsMetaData.getColumnName(i + 1));
                result.add(i, column);
            }
            while (rs.next()) {
                for (int i = 0; i < numOfCols; i++) {
                    List<String> column = result.get(i);
                    column.add(rs.getString(i + 1));
                }
            }
        } catch (SQLException e) {
            throw new DAOException("Error while executing query:" + query, e);
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    throw new DAOException("Error closing result set", e);
                }
            }
        }
        return result;
    }

    /**
     * This method is required to write to the database, using methods such as INSERT, UPDATE
     * and DELETE.
     * 
     * @param query
     * @return the number of rows modified.
     * @throws DAOException
     */
    public int write(String query) throws DAOException {
        int rows = 0;
        Statement stmt = null;
        try {
            stmt = this.dbCon.createStatement();
            rows = stmt.executeUpdate(query);
        } catch (SQLException e) {
            throw new DAOException("Error while executing query:" + query, e);
        }
        return rows;
    }

    public void manage(String query) {

    }
}